package com.hnkywl.sts.dao.report;

import com.hnkywl.sts.dao.organ.GsjgDao;
import com.hnkywl.sts.entity.organ.Gsjg;
import com.hnkywl.sts.entity.organ.Nbbm;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.Query;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-6-15
 * Time: 下午9:13
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class XslrDao extends SimpleHibernateTemplate {
    /**
     * 票据库存报表应收
     *
     * @param gsid
     * @param
     * @return
     */
//    public Map<String, Object> xslrList(Long gsid,String startDate, String endDate) {
//        if (gsid == null)
//            gsid = -1L;
//        Connection con = null;
//        CallableStatement call = null;
//        ResultSet rs = null;
//        Integer total=0;
//        try {
//            con = getSession().connection();
//            call = con.prepareCall("{Call RPT_CW_XSLR(?,?,?,?)}");
//            call.setString(1, startDate);
//            call.setString(2, endDate);
//            call.setLong(3, gsid);
//            call.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);
//            call.execute();
//            //====================================
//            rs = ((OracleCallableStatement) call).getCursor(4);
//            ResultSetMetaData resultsMetaData = rs.getMetaData();
//            int columnCount = resultsMetaData.getColumnCount();
//            Map<String, Object> result = new HashMap<String, Object>();
//            List<Map> rows = new ArrayList<Map>();
//            List<Map> footer = new ArrayList<Map>();
//            while (rs.next()) {
//                Map<String, Object> r = new HashMap<String, Object>();
//                for (int i = 1; i <= columnCount; i++) {
//                    String columnName = resultsMetaData.getColumnName(i).toLowerCase();
//                    r.put(columnName, rs.getObject(i));
//                }
//                if ("1".equals(r.get("flag").toString()))
//                    rows.add(r);
//                else
//                    footer.add(r);
//            }
//            result.put("rows", rows);
//            result.put("footer", footer);
//            return result;
//        } catch (SQLException e) {
//            e.printStackTrace();
//            throw new RuntimeException(e.getMessage());
//        } finally {
//            closeResource(rs, call, con);
//        }
//    }

    private GsjgDao gsjgDao;

    @Autowired
    public void setGsjgDao(GsjgDao gsjgDao) {
        this.gsjgDao = gsjgDao;
    }

    /**
     * @param gsid      机构ID
     * @param startDate 开始时间
     * @param endDate   结束时间
     * @param khid      客户id
     * @param xslx      0、全部  1、库存销售 2、直达销售
     * @param jszt      0、全部  1、已结算 2、未结算
     * @return
     */
    public Map<String, List<Map>> xslrList(Long gsid, Date startDate, Date endDate, Long khid, Integer xslx, Integer jszt) {
        //判断传入过来的公司机构是公司还是经营部门
        String jgWhere = "";
        if (gsid != null) {
            Gsjg gsjg = gsjgDao.get(gsid);
            if (gsjg instanceof Nbbm) {
                jgWhere = " AND T1.SBBM_ID = " + gsid.toString();
            } else {
                jgWhere = " AND T1.SBGS_ID = " + gsid.toString();
            }

            if (khid != null) {
                jgWhere += " AND T1.KH_ID = " + khid.toString();
            }
        }

        if (xslx == 1) {
            jgWhere += " AND T1.XSLX != 'zddh' ";
        } else if (xslx == 2) {
            jgWhere += " AND T1.XSLX = 'zddh' ";
        }

        if (jszt == 1) {
            jgWhere += " AND NVL(T9.SHZT,0) = '1' ";
        } else if (jszt == 2) {
            jgWhere += " AND NVL(T9.SHZT,0) = '0' ";
        }

        if (jszt == 1) {
            jgWhere += " AND T9.KPRQ >= :startDate AND T9.KPRQ <= :endDate ";
        } else {
            jgWhere += " AND T1.CKSJ >= :startDate AND T1.CKSJ <= :endDate ";
        }

        String sqlRows = "SELECT XSD_ID XSDID,GS,BM,XSDH,CKSJ,KH,GGMC,GG,DW,TH,CD,CKSL,XSJ,XSE,XSCB,XSE - XSCB XSLR,YJS,JSSJ FROM \n" +
                "(SELECT T.XSD_ID,T3.JC GS,T4.JC BM,T1.XSDH XSDH,TO_CHAR(T1.CKSJ,'yyyy-mm-dd') CKSJ,T5.JC KH,T7.Z CD,T2.NAME GGMC,T2.CZ || ' ' || T2.GGXH GG," +
                "T2.DW DW,T2.TH TH,T.CK_SBSL CKSL,ROUND(T.BHSJ,4) XSJ,ROUND(ROUND(T.HSJ*T.CK_SBSL,2)/(1+T1.SL/100),2) XSE,\n" +
                "ROUND(T.XSCB * T.CK_SBSL,2) XSCB,\n" +
                "DECODE(NVL(T9.SHZT,0),1,'已结算','未结算') YJS,\n" +
                "TO_CHAR(T9.KPRQ,'YYYY-MM-DD') JSSJ\n" +
                "FROM T_BIZ_XSMX T \n" +
                "LEFT JOIN T_BIZ_XSD T1 ON T.XSD_ID = T1.ID\n" +
                "LEFT JOIN T_SYS_GG T2 ON T.JTWZ_ID = T2.ID\n" +
                "LEFT JOIN T_SYS_GSJG T3 ON T1.SBGS_ID = T3.ID\n" +
                "LEFT JOIN T_SYS_GSJG T4 ON T1.SBBM_ID = T4.ID\n" +
                "LEFT JOIN T_SYS_GSJG T5 ON T1.KH_ID = T5.ID\n" +
                "LEFT JOIN T_BIZ_KH T6 ON T.KH_ID = T6.ID\n" +
                "LEFT JOIN T_SYS_SJZDZ T7 ON T6.CD_SJZDZ_ID = T7.ID\n" +
                "LEFT JOIN T_BIZ_JSD T9 ON T9.ID = T.XSJSD_ID\n" +
                "WHERE T1.ID IS NOT NULL AND T1.DELETED = 0 AND T.DELETED = 0 AND T1.YCK = 1 " +
                jgWhere +
                " ORDER BY T1.SBBM_ID, T1.ID ,T.ID)";
        Query queryRows = getSession().createSQLQuery(sqlRows);
        queryRows.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        if (startDate != null) {
            Date start = sDaysAfterNowDate(startDate);
            queryRows.setParameter("startDate", start);
        }

        if (endDate != null) {
            Date end = jDaysAfterNowDate(endDate);
            queryRows.setParameter("endDate", end);
        }

        //应业务要求销售不含税额以含税额/1.17保留2位计算，销售成本也以成本额保留2位计算，所以跟明细有一点差额
        String sqlFooter = "SELECT SUM(XSE) XSE,SUM(XSCB) XSCB,SUM(XSE - XSCB) XSLR FROM (\n" +
                "SELECT ROUND(ROUND(T.CK_SBSL * T.HSJ,2)/(1 + T1.Sl/100),2) XSE,\n" +
                "ROUND(T.XSCB * T.CK_SBSL,2) XSCB\n" +
                "FROM T_BIZ_XSMX T \n" +
                "LEFT JOIN T_BIZ_XSD T1 ON T.XSD_ID = T1.ID\n" +
                "LEFT JOIN T_BIZ_KH T6 ON T.KH_ID = T6.ID\n" +
                "LEFT JOIN T_SYS_SJZDZ T7 ON T6.CD_SJZDZ_ID = T7.ID\n" +
                "LEFT JOIN T_BIZ_JSD T9 ON T9.ID = T.XSJSD_ID\n" +
                "WHERE T1.ID IS NOT NULL AND T1.DELETED = 0 AND T.DELETED = 0 AND T1.YCK = 1\n" +
                jgWhere +
                ")";
        Query queryFooter = getSession().createSQLQuery(sqlFooter);
        queryFooter.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        if (startDate != null) {
            Date start = sDaysAfterNowDate(startDate);
            queryFooter.setParameter("startDate", start);
        }

        if (endDate != null) {
            Date end = jDaysAfterNowDate(endDate);
            queryFooter.setParameter("endDate", end);
        }

        Map<String, List<Map>> result = new HashMap<String, List<Map>>();
        result.put("rows", queryRows.list());
        result.put("footer", queryFooter.list());
        return result;
    }


    public Map<String, List<Map>> xslrhzList(Long gsid, Date startDate, Date endDate, Integer xslx, Integer jszt) {
        //判断传入过来的公司机构是公司还是经营部门
        String jgWhere = "";
        if (gsid != null) {
            Gsjg gsjg = gsjgDao.get(gsid);
            if (gsjg instanceof Nbbm) {
                jgWhere = " AND T1.SBBM_ID = " + gsid.toString();
            } else {
                jgWhere = " AND T1.SBGS_ID = " + gsid.toString();
            }
        }

        if (xslx == 1) {
            jgWhere += " AND T1.XSLX != 'zddh' ";
        } else if (xslx == 2) {
            jgWhere += " AND T1.XSLX = 'zddh' ";
        }

        if (jszt == 1) {
            jgWhere += " AND NVL(T9.SHZT,0) = '1' ";
        } else if (jszt == 2) {
            jgWhere += " AND NVL(T9.SHZT,0) = '0' ";
        }

        if (jszt == 1) {
            jgWhere += " AND T9.KPRQ >= :startDate AND T9.KPRQ <= :endDate ";
        } else {
            jgWhere += " AND T1.CKSJ >= :startDate AND T1.CKSJ <= :endDate ";
        }

        String sqlRows = "SELECT GSID,BMID,GS,BM,sum(CKSL) CKSL,sum(XSE) XSE,sum(XSCB) XSCB,sum(XSE - XSCB) XSLR " +
                " FROM (SELECT T3.ID GSID,T4.ID BMID,T3.JC GS,T4.JC BM, T.CK_SBSL CKSL," +
                " ROUND(ROUND(T.HSJ * T.CK_SBSL, 2) / (1 + T1.SL / 100), 2) XSE," +
                " ROUND(T.XSCB * T.CK_SBSL, 2) XSCB " +
                " FROM T_BIZ_XSMX T LEFT JOIN T_BIZ_XSD T1 ON T.XSD_ID = T1.ID LEFT JOIN T_SYS_GG T2 ON T.JTWZ_ID = T2.ID LEFT JOIN T_SYS_GSJG T3 ON T1.SBGS_ID = T3.ID LEFT JOIN T_SYS_GSJG T4 ON T1.SBBM_ID = T4.ID LEFT JOIN T_SYS_GSJG T5 ON T1.KH_ID = T5.ID LEFT JOIN T_BIZ_KH T6 ON T.KH_ID = T6.ID LEFT JOIN T_SYS_SJZDZ T7 ON T6.CD_SJZDZ_ID = T7.ID LEFT JOIN T_BIZ_JSD T9 ON T9.ID = T.XSJSD_ID WHERE T1.ID IS NOT NULL  AND T1.DELETED = 0 AND T.DELETED = 0 AND T1.YCK = 1 " +
                jgWhere +
                " ORDER BY T1.SBBM_ID, T1.ID, T.ID) GROUP BY GSID,BMID,GS,BM ORDER BY GSID,BMID";
        Query queryRows = getSession().createSQLQuery(sqlRows);
        queryRows.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        if (startDate != null) {
            Date start = sDaysAfterNowDate(startDate);
            queryRows.setParameter("startDate", start);
        }

        if (endDate != null) {
            Date end = jDaysAfterNowDate(endDate);
            queryRows.setParameter("endDate", end);
        }
        Map<String, List<Map>> result = new HashMap<String, List<Map>>();
        result.put("rows", queryRows.list());
        return result;
    }

    /**
     * 大屏列表：销售利润列表
     */
    public Map<String, Object> xslrListForScreen(Date startDate, Date endDate, int pageNo, int pageSize) {
        String jgWhere = "";
        jgWhere += " AND T1.CKSJ >= :startDate AND T1.CKSJ <= :endDate ";
        String sqlRows = "SELECT GSID,GS,BM,SUM(CKSL) CKSL,SUM(XSJ) XSJ,SUM(XSE) XSE,SUM(XSCB) XSCB,SUM(XSE - XSCB) XSLR " +
                "FROM (SELECT T.XSD_ID,T3.JC GS,T3.ID GSID,T4.JC BM,T1.XSDH XSDH,TO_CHAR(T1.CKSJ, 'yyyy-mm-dd') CKSJ,T5.JC KH, " +
                " T7.Z CD,T2.NAME GGMC,T2.CZ || ' ' || T2.GGXH GG,T2.DW DW,T2.TH TH,T.CK_SBSL CKSL,ROUND(T.BHSJ, 4) XSJ, " +
                " ROUND(ROUND(T.HSJ * T.CK_SBSL, 2) / (1 + T1.SL / 100), 2) XSE,ROUND(T.XSCB * T.CK_SBSL, 2) XSCB, " +
                " TO_CHAR(T9.KPRQ, 'YYYY-MM-DD') JSSJ FROM T_BIZ_XSMX T LEFT JOIN T_BIZ_XSD T1 ON T.XSD_ID = T1.ID " +
                " LEFT JOIN T_SYS_GG T2 ON T.JTWZ_ID = T2.ID LEFT JOIN T_SYS_GSJG T3 ON T1.SBGS_ID = T3.ID " +
                " LEFT JOIN T_SYS_GSJG T4 ON T1.SBBM_ID = T4.ID LEFT JOIN T_SYS_GSJG T5 ON T1.KH_ID = T5.ID " +
                " LEFT JOIN T_BIZ_KH T6 ON T.KH_ID = T6.ID LEFT JOIN T_SYS_SJZDZ T7 ON T6.CD_SJZDZ_ID = T7.ID " +
                " LEFT JOIN T_BIZ_JSD T9 ON T9.ID = T.XSJSD_ID WHERE T1.ID IS NOT NULL " +
                " AND T1.DELETED = 0 AND T.DELETED = 0 AND T1.YCK = 1 " + jgWhere +
                ") GROUP BY GSID, GS, BM ORDER BY GSID, BM";
        String countSql = "SELECT COUNT(1) " +
                "FROM (SELECT T3.ID FROM T_BIZ_XSMX T LEFT JOIN T_BIZ_XSD T1 ON T.XSD_ID = T1.ID " +
                " LEFT JOIN T_SYS_GG T2 ON T.JTWZ_ID = T2.ID LEFT JOIN T_SYS_GSJG T3 ON T1.SBGS_ID = T3.ID " +
                " LEFT JOIN T_SYS_GSJG T4 ON T1.SBBM_ID = T4.ID LEFT JOIN T_SYS_GSJG T5 ON T1.KH_ID = T5.ID " +
                " LEFT JOIN T_BIZ_KH T6 ON T.KH_ID = T6.ID LEFT JOIN T_SYS_SJZDZ T7 ON T6.CD_SJZDZ_ID = T7.ID " +
                " LEFT JOIN T_BIZ_JSD T9 ON T9.ID = T.XSJSD_ID WHERE T1.ID IS NOT NULL " +
                " AND T1.DELETED = 0 AND T.DELETED = 0 AND T1.YCK = 1 " + jgWhere +
                " GROUP BY T3.ID, T4.JC, T1.CKSJ, T2.NAME, T2.GGXH,T2.CZ)";
        Query queryRows = getSession().createSQLQuery(sqlRows);
        Query countQuery = getSession().createSQLQuery(countSql);
        queryRows.setFirstResult((pageNo - 1) * pageSize);
        queryRows.setMaxResults(pageSize);
        queryRows.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        if (startDate != null) {
            Date start = sDaysAfterNowDate(startDate);
            queryRows.setParameter("startDate", start);
            countQuery.setParameter("startDate", start);
        }

        if (endDate != null) {
            Date end = jDaysAfterNowDate(endDate);
            queryRows.setParameter("endDate", end);
            countQuery.setParameter("endDate", end);
        }

        //应业务要求销售不含税额以含税额/1.17保留2位计算，销售成本也以成本额保留2位计算，所以跟明细有一点差额
        String sqlFooter = "SELECT SUM(XSE) XSE,SUM(XSCB) XSCB,SUM(XSE - XSCB) XSLR FROM ( " +
                "SELECT ROUND(ROUND(T.CK_SBSL * T.HSJ,2)/(1 + T1.Sl/100),2) XSE, " +
                "ROUND(T.XSCB * T.CK_SBSL,2) XSCB " +
                "FROM T_BIZ_XSMX T \n" +
                "LEFT JOIN T_BIZ_XSD T1 ON T.XSD_ID = T1.ID " +
                "LEFT JOIN T_BIZ_KH T6 ON T.KH_ID = T6.ID " +
                "LEFT JOIN T_SYS_SJZDZ T7 ON T6.CD_SJZDZ_ID = T7.ID " +
                "LEFT JOIN T_BIZ_JSD T9 ON T9.ID = T.XSJSD_ID " +
                "WHERE T1.ID IS NOT NULL AND T1.DELETED = 0 AND T.DELETED = 0 AND T1.YCK = 1 " +
                jgWhere +
                ")";
        Query queryFooter = getSession().createSQLQuery(sqlFooter);
        queryFooter.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        if (startDate != null) {
            Date start = sDaysAfterNowDate(startDate);
            queryFooter.setParameter("startDate", start);
        }

        if (endDate != null) {
            Date end = jDaysAfterNowDate(endDate);
            queryFooter.setParameter("endDate", end);
        }

        Map<String, Object> result = new HashMap<String, Object>();
        result.put("rows", queryRows.list());
        result.put("footer", queryFooter.list());
        result.put("total", countQuery.uniqueResult());
        return result;
    }

    public Map<String, Object> findXslrPieForScreen(Date startDate, Date endDate) {
        String jgWhere = "";
        jgWhere += " AND T1.CKSJ >= :startDate AND T1.CKSJ <= :endDate ";
        String sqlRows = "SELECT GSID,GS||'-'||BM AS GS,SUM(CKSL) CKSL,SUM(XSJ) XSJ,SUM(XSE) XSE,SUM(XSCB) XSCB,SUM(XSE - XSCB) XSLR " +
                "FROM (SELECT T.XSD_ID,T3.JC GS,T3.ID GSID,T4.JC BM,T1.XSDH XSDH,TO_CHAR(T1.CKSJ, 'yyyy-mm-dd') CKSJ,T5.JC KH, " +
                " T7.Z CD,T2.NAME GGMC,T2.CZ || ' ' || T2.GGXH GG,T2.DW DW,T2.TH TH,T.CK_SBSL CKSL,ROUND(T.BHSJ, 4) XSJ, " +
                " ROUND(ROUND(T.HSJ * T.CK_SBSL, 2) / (1 + T1.SL / 100), 2) XSE,ROUND(T.XSCB * T.CK_SBSL, 2) XSCB, " +
                " TO_CHAR(T9.KPRQ, 'YYYY-MM-DD') JSSJ FROM T_BIZ_XSMX T LEFT JOIN T_BIZ_XSD T1 ON T.XSD_ID = T1.ID " +
                " LEFT JOIN T_SYS_GG T2 ON T.JTWZ_ID = T2.ID LEFT JOIN T_SYS_GSJG T3 ON T1.SBGS_ID = T3.ID " +
                " LEFT JOIN T_SYS_GSJG T4 ON T1.SBBM_ID = T4.ID LEFT JOIN T_SYS_GSJG T5 ON T1.KH_ID = T5.ID " +
                " LEFT JOIN T_BIZ_KH T6 ON T.KH_ID = T6.ID LEFT JOIN T_SYS_SJZDZ T7 ON T6.CD_SJZDZ_ID = T7.ID " +
                " LEFT JOIN T_BIZ_JSD T9 ON T9.ID = T.XSJSD_ID WHERE T1.ID IS NOT NULL " +
                " AND T1.DELETED = 0 AND T.DELETED = 0 AND T1.YCK = 1 " + jgWhere +
                ") GROUP BY GSID, GS, BM ORDER BY GSID, BM";
        Query queryRows = getSession().createSQLQuery(sqlRows);
        queryRows.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        if (startDate != null) {
            Date start = sDaysAfterNowDate(startDate);
            queryRows.setParameter("startDate", start);
        }

        if (endDate != null) {
            Date end = jDaysAfterNowDate(endDate);
            queryRows.setParameter("endDate", end);
        }
        Map<String, Object> result = new HashMap<String, Object>();
        result.put("rows", queryRows.list());
        return result;
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public Date jDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 23);
        rightNow.set(java.util.Calendar.MINUTE, 59);
        rightNow.set(java.util.Calendar.SECOND, 59);
        return rightNow.getTime();
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public Date sDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 0);
        rightNow.set(java.util.Calendar.MINUTE, 0);
        rightNow.set(java.util.Calendar.SECOND, 0);
        return rightNow.getTime();
    }


    private void closeResource(ResultSet rs, Statement s, Connection con) {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (s != null)
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (con != null)
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
    }
}
